<?php
  include '../dbmng/dbconfig.php';

  $itemsPerPage = 7; // For LIMIT clause of SQL query.
  $dbconn = new mysqli(HOST, USERNAME, PASSWORD, DBNAME, PORT);
  // Format the SQL query statment.
  $statStrForRowCount = 'SELECT s.spe_no
                         FROM cat_spe AS s
                         INNER JOIN (SELECT g.gen_no,
					                                  f.fam_no,
					                                  g.gen_nm_la,
					                                  g.gen_nm_ch,
                                            f.fam_nm_ch,
                                            f.fam_nm_la
			                               FROM cat_gen AS g
			                               INNER JOIN cat_fam AS f
			                               ON g.fam_no = f.fam_no) AS n
                         ON s.gen_no = n.gen_no
                         WHERE n.gen_nm_ch REGEXP "(%s){1,}"
	                          OR n.gen_nm_la REGEXP "(%s){1,}"
                            OR n.fam_nm_la REGEXP "(%s){1,}"
                            OR n.fam_nm_ch REGEXP "(%s){1,}"
	                          OR s.spe_nm_ch REGEXP "(%s){1,}"
                            OR s.spe_nm_la REGEXP "(%s){1,}"
                         ORDER BY spe_no ASC;';
  $statStrForContent = 'SELECT s.spe_no AS "speciesNo",
	                            s.spe_nm_la AS "speciesLatinName",
	                            s.spe_nm_ch AS "speciesChineseName",
                              n.gen_nm_ch AS "generaChineseName",
                              n.gen_nm_la AS "generaLatinName",
                              n.fam_nm_ch AS "familyChineseName",
                              n.fam_nm_la AS "familyLatinName"
                       FROM cat_spe AS s
                       INNER JOIN (SELECT g.gen_no,
					                                f.fam_no,
					                                g.gen_nm_la,
					                                g.gen_nm_ch,
                                          f.fam_nm_ch,
                                          f.fam_nm_la
			                             FROM cat_gen AS g
			                             INNER JOIN cat_fam AS f
			                             ON g.fam_no = f.fam_no) AS n
                       ON s.gen_no = n.gen_no
                       WHERE n.gen_nm_ch REGEXP "(%s){1,}"
	                        OR n.gen_nm_la REGEXP "(%s){1,}"
                          OR n.fam_nm_la REGEXP "(%s){1,}"
                          OR n.fam_nm_ch REGEXP "(%s){1,}"
	                        OR s.spe_nm_ch REGEXP "(%s){1,}"
                          OR s.spe_nm_la REGEXP "(%s){1,}"
                       ORDER BY spe_no ASC
                       LIMIT %d OFFSET %d;';

  $sqlStatForRowCount = sprintf($statStrForRowCount, $searchname, $searchname, $searchname, $searchname, $searchname, $searchname);
  // Exectute the SQL query.
  if ($sqlQueryForRowCount = $dbconn->query($sqlStatForRowCount)){
    $resultRowNum = $sqlQueryForRowCount->num_rows;
  }
  echo 'return result tuple numbers: ' . $resultRowNum;
  $sqlStatForContent = sprintf($statStrForContent, $searchname, $searchname, $searchname, $searchname, $searchname, $searchname, $itemsPerPage, (($page-1)*$itemsPerPage));
  if ($sqlQueryForContent = $dbconn->query($sqlStatForContent)){
    // Extract the corespondent data.
    while($sqlResultForContent = $sqlQueryForContent->fetch_assoc()){
      $specieNo = $sqlResultForContent['speciesNo'];
      $specieLatinName = $sqlResultForContent['speciesLatinName'];
      $generaLatinName = $sqlResultForContent['generaLatinName'];
      $familyLatinName = $sqlResultForContent['familyLatinName'];
      // Format the HTML ouput with query result.
      /**
      $file = "../../view/item.html";
      $handle = fopen($file, "r");
      $content = fread($handle, filesize($file));
      $output = sprintf($content, $specieNo, $specieNo, $specieLatinName, $generaLatinName, $familyLatinName);
      print($output);
      fclose($handle);
      **/
      include '../../view/item.php';
    }
  }
  $dbconn->close;
 ?>
